#!/usr/bin/env python
# -*- coding:utf-8 -*-

from controller.dictionaryController import DictionaryController

import datetime
import math

from flask import current_app, jsonify
from sqlalchemy import or_,and_
from werkzeug.security import generate_password_hash, check_password_hash

from app import db
from models.dictionary import Dictionary
from models.word import Word
from models.word_dictionary import WordDictionary
from utils import commons
from utils.generate_id import GenerateID
from utils.loggings import loggings
from utils.response_code import RET, error_map_EN
from utils.rsa_encryption_decryption import RSAEncryptionDecryption

import random

class DictionaryService(DictionaryController):

    # 以文本文件添加词典
    @classmethod
    def add_dict(cls, filename, file_content):
        try:
            # Step 1: Insert into dictionary table
            dictionary_id = GenerateID.create_random_id()
            new_dictionary = Dictionary(
                dictionary_id=dictionary_id,
                dictionary_name=filename,
                is_delete=0,
                create_time=datetime.datetime.now(),
                update_time=datetime.datetime.now()
            )
            db.session.add(new_dictionary)
            db.session.commit()

            # Step 2: Process file content and insert into word and word_dictionary tables
            words = file_content.strip().split('\n')
            for word_line in words:
                word_data = word_line.split()
                # 看是否是四个部分
                if len(word_data) != 4:
                    loggings.error(f"Invalid line in file: {word_line}")
                    continue

                word_name, word_soundmark, word_paraphrase, word_pronounce = word_data

                # Check if the word already exists in the word table
                existing_word = db.session.query(Word).filter(Word.word_name == word_name).first()
                if not existing_word:
                    # Insert into word table
                    new_word = Word(
                        word_id=GenerateID.create_random_id(),
                        word_name=word_name,
                        word_soundmark=word_soundmark,
                        word_paraphrase=word_paraphrase,
                        word_pronounce=word_pronounce,
                        is_delete=0,
                        create_time=datetime.datetime.now(),
                        update_time=datetime.datetime.now()
                    )
                    db.session.add(new_word)
                    db.session.commit()
                    word_id = new_word.word_id
                else:
                    word_id = existing_word.word_id

                # Insert into word_dictionary table
                new_word_dictionary = WordDictionary(
                    word_dictionary_id=GenerateID.create_random_id(),
                    word_id=word_id,
                    dictionary_id=dictionary_id,
                    is_test=0,
                    is_delete=0,
                    create_time=datetime.datetime.now(),
                    update_time=datetime.datetime.now()
                )
                db.session.add(new_word_dictionary)
                db.session.commit()

            data_dict = {
                'dictionary_id': dictionary_id,
                'dictionary_name': filename,
                'create_time': datetime.datetime.now(),
            }
            return {'code': RET.OK, 'message': '添加成功', 'data': data_dict}

        except Exception as e:
            db.session.rollback()
            loggings.exception(1, e)
            return {'code': RET.DBERR, 'message': '数据库异常，添加词典失败', 'data': str(e)}

        finally:
            db.session.close()


    # 删除词典
    @classmethod
    def delete_dict(cls, dictionary_id):
        try:
            dictionary = db.session.query(Dictionary).filter(Dictionary.dictionary_id == dictionary_id).first()
            if not dictionary:
                return {'code': RET.NODATA, 'message': '词典不存在', 'data': None}

            dictionary.is_delete = 1
            dictionary.update_time = datetime.datetime.now()
            db.session.commit()

            data_dict = {
                'dictionary_id': dictionary.dictionary_id,
                'dictionary_name': dictionary.dictionary_name,
                'delete_time': dictionary.update_time,
            }

            return {'code': RET.OK, 'message': '词典删除成功', 'data': data_dict}

        except Exception as e:
            db.session.rollback()
            loggings.exception(1, e)
            return {'code': RET.DBERR, 'message': '数据库异常，删除词典失败', 'data': str(e)}

        finally:
            db.session.close()

    # 查询词典中的所有单词
    @classmethod
    def get_words_by_dictionary_id(cls, dictionary_id):
        try:
            # 查询词典是否存在
            dictionary = db.session.query(Dictionary).filter(Dictionary.dictionary_id == dictionary_id).first()
            if not dictionary:
                return {'code': RET.NODATA, 'message': '词典不存在', 'data': None}

            # 查询词典中的所有单词及其是否为测试题的信息
            words = db.session.query(
                Word.word_id,
                Word.word_name,
                Word.word_soundmark,
                Word.word_paraphrase,
                Word.word_pronounce,
                Word.create_time,
                Word.update_time,
                WordDictionary.is_test
            ).join(
                WordDictionary,
                and_(
                    Word.word_id == WordDictionary.word_id,
                    WordDictionary.dictionary_id == dictionary_id,
                    WordDictionary.is_delete == 0,
                    Word.is_delete == 0
                )
            ).all()

            word_list = [
                {
                    'word_id': word.word_id,
                    'word_name': word.word_name,
                    'word_soundmark': word.word_soundmark,
                    'word_paraphrase': word.word_paraphrase,
                    'word_pronounce': word.word_pronounce,
                    'create_time': word.create_time,
                    'update_time': word.update_time,
                    'is_test': word.is_test  # 添加是否为测试题的字段
                }
                for word in words
            ]

            return {'code': RET.OK, 'message': '查询成功', 'data': word_list}

        except Exception as e:
            db.session.rollback()
            loggings.exception(1, e)
            return {'code': RET.DBERR, 'message': '数据库异常，查询词典单词失败', 'data': str(e)}

        finally:
            db.session.close()

    # 删除词典中的单词
    @classmethod
    def delete_word_from_dict(cls, dictionary_id, word_id):
        try:
            # 查询词典是否存在
            dictionary = db.session.query(Dictionary).filter(Dictionary.dictionary_id == dictionary_id).first()
            if not dictionary:
                return {'code': RET.NODATA, 'message': '词典不存在', 'data': None}

            # 查询单词在词典中的记录是否存在
            word_dict_record = db.session.query(WordDictionary).filter(
                WordDictionary.word_id == word_id,
                WordDictionary.dictionary_id == dictionary_id,
                WordDictionary.is_delete == 0
            ).first()

            if not word_dict_record:
                return {'code': RET.NODATA, 'message': '单词不在词典中', 'data': None}

            # 逻辑删除单词记录
            word_dict_record.is_delete = 1
            word_dict_record.update_time = datetime.datetime.now()
            db.session.commit()

            data_dict = {
                'word_id': word_id,
                'dictionary_id': dictionary_id,
                'delete_time': word_dict_record.update_time,
            }

            return {'code': RET.OK, 'message': '单词删除成功', 'data': data_dict}

        except Exception as e:
            db.session.rollback()
            loggings.exception(1, e)
            return {'code': RET.DBERR, 'message': '数据库异常，删除单词失败', 'data': str(e)}

        finally:
            db.session.close()

    # 将单词设置为题库
    @classmethod
    def set_word_as_test(cls, dictionary_id, word_id):
        try:
            # 查询词典是否存在
            dictionary = db.session.query(Dictionary).filter(Dictionary.dictionary_id == dictionary_id).first()
            if not dictionary:
                return {'code': RET.NODATA, 'message': '词典不存在', 'data': None}

            # 查询单词在词典中的记录是否存在
            word_dict_record = db.session.query(WordDictionary).filter(
                WordDictionary.word_id == word_id,
                WordDictionary.dictionary_id == dictionary_id,
                WordDictionary.is_delete == 0
            ).first()

            if not word_dict_record:
                return {'code': RET.NODATA, 'message': '单词不在词典中', 'data': None}

            # 更新 is_test 字段
            word_dict_record.is_test = 1
            word_dict_record.update_time = datetime.datetime.now()
            db.session.commit()

            data_dict = {
                'word_id': word_id,
                'dictionary_id': dictionary_id,
                'is_test': 1,
                'update_time': word_dict_record.update_time,
            }

            return {'code': RET.OK, 'message': '题库设置成功', 'data': data_dict}

        except Exception as e:
            db.session.rollback()
            loggings.exception(1, e)
            return {'code': RET.DBERR, 'message': '数据库异常，题库设置失败', 'data': str(e)}

        finally:
            db.session.close()

    # 将单词从题库中移除
    @classmethod
    def cancel_word_as_test(cls, dictionary_id, word_id):
        try:
            # 查询词典是否存在
            dictionary = db.session.query(Dictionary).filter(Dictionary.dictionary_id == dictionary_id).first()
            if not dictionary:
                return {'code': RET.NODATA, 'message': '词典不存在', 'data': None}

            # 查询单词在词典中的记录是否存在
            word_dict_record = db.session.query(WordDictionary).filter(
                WordDictionary.word_id == word_id,
                WordDictionary.dictionary_id == dictionary_id,
                WordDictionary.is_delete == 0
            ).first()

            if not word_dict_record:
                return {'code': RET.NODATA, 'message': '单词不在词典中', 'data': None}

            # 更新 is_test 字段
            word_dict_record.is_test = 0
            word_dict_record.update_time = datetime.datetime.now()
            db.session.commit()

            data_dict = {
                'word_id': word_id,
                'dictionary_id': dictionary_id,
                'is_test': 0,
                'update_time': word_dict_record.update_time,
            }

            return {'code': RET.OK, 'message': '题库设置成功', 'data': data_dict}

        except Exception as e:
            db.session.rollback()
            loggings.exception(1, e)
            return {'code': RET.DBERR, 'message': '数据库异常，题库设置失败', 'data': str(e)}

        finally:
            db.session.close()

    # 根据词典名称或名称的一部分进行查找
    @classmethod
    def search_dictionaries_by_name(cls, name):
        try:
            # 使用 ilike 进行不区分大小写的模糊查询
            dictionaries = db.session.query(Dictionary).filter(
                Dictionary.dictionary_name.ilike(f'%{name}%'),
                Dictionary.is_delete == 0
            ).all()

            if not dictionaries:
                return {'code': RET.NODATA, 'message': '没有找到匹配的词典', 'data': None}

            dictionary_list = [
                {
                    'dictionary_id': dictionary.dictionary_id,
                    'dictionary_name': dictionary.dictionary_name,
                    'create_time': dictionary.create_time,
                    'update_time': dictionary.update_time
                }
                for dictionary in dictionaries
            ]

            return {'code': RET.OK, 'message': '查询成功', 'data': dictionary_list}

        except Exception as e:
            db.session.rollback()
            loggings.exception(1, e)
            return {'code': RET.DBERR, 'message': '数据库异常，查询词典失败', 'data': str(e)}

        finally:
            db.session.close()

    @classmethod
    def search_words_in_dictionary(cls,dictionary_id, word):
        try:
            # 使用 join 查询来关联 word 和 word_dictionary 表
            words = db.session.query(Word).join(
                WordDictionary,
                and_(
                    Word.word_id == WordDictionary.word_id,
                    WordDictionary.dictionary_id == dictionary_id,
                    WordDictionary.is_delete == 0,
                    Word.is_delete == 0
                )
            ).filter(Word.word_name.like(f'%{word}%')).all()

            if words:
                return {
                    'code': RET.OK,
                    'message': '查询成功',
                    'data': [{'word_id': w.word_id, 'word_name': w.word_name, 'definition': w.word_paraphrase} for w in words]
                }
            else:
                return {'code': RET.NODATA, 'message': '未找到匹配的单词', 'data': None}
        except Exception as e:
            loggings.exception(1, e)
            return {'code': RET.DBERR, 'message': str(e), 'data': None}
        finally:
            db.session.close()

    # 重命名词典
    @classmethod
    def rename_dict(cls, dictionary_id, new_name):
        try:
            # 查询词典是否存在
            dictionary = db.session.query(Dictionary).filter(Dictionary.dictionary_id == dictionary_id).first()
            if not dictionary:
                return {'code': RET.NODATA, 'message': '词典不存在', 'data': None}

            # 更新词典名称
            dictionary.dictionary_name = new_name
            dictionary.update_time = datetime.datetime.now()
            db.session.commit()

            data_dict = {
                'dictionary_id': dictionary.dictionary_id,
                'dictionary_name': dictionary.dictionary_name,
                'update_time': dictionary.update_time,
            }

            return {'code': RET.OK, 'message': '词典重命名成功', 'data': data_dict}

        except Exception as e:
            db.session.rollback()
            loggings.exception(1, e)
            return {'code': RET.DBERR, 'message': '数据库异常，重命名词典失败', 'data': str(e)}

        finally:
            db.session.close()


    # 生成测试题目
    @classmethod
    def generate_test_questions(cls, dictionary_id, num_questions=10):
        try:
            # 获取词典中的所有单词
            words = db.session.query(Word).join(
                WordDictionary,
                and_(
                    Word.word_id == WordDictionary.word_id,
                    WordDictionary.dictionary_id == dictionary_id,
                    WordDictionary.is_test == 1,
                    WordDictionary.is_delete == 0,
                    Word.is_delete == 0
                )
            ).all()

            if len(words) < num_questions:
                return {'code': RET.NODATA, 'message': '题库中的单词不足', 'data': None}

            # 随机选择单词
            selected_words = random.sample(words, num_questions)
            test_questions = []

            for word in selected_words:
                # 获取正确选项
                correct_option = word.word_paraphrase

                # 获取其他三个选项
                other_words = db.session.query(Word).filter(
                    Word.word_id != word.word_id,
                    Word.is_delete == 0
                ).all()
                other_options = [w.word_paraphrase for w in random.sample(other_words, 3)]

                # 组合选项
                options = [correct_option] + other_options
                random.shuffle(options)

                # 记录正确选项的索引
                correct_option_index = options.index(correct_option)

                # 添加到测试题目列表
                test_questions.append({
                    'word_id': word.word_id,
                    'word_name': word.word_name,
                    'options': options,
                    'correct_option': correct_option_index+1
                })

            return {'code': RET.OK, 'message': '生成测试题目成功', 'data': test_questions}

        except Exception as e:
            db.session.rollback()
            loggings.exception(1, e)
            return {'code': RET.DBERR, 'message': '数据库异常，生成测试题目失败', 'data': str(e)}

        finally:
            db.session.close()

